# @Project: diablo
# @File: test
# @Time: 2023/7/3 10:34
# @User: Jolly
# @Email: jolly@007vin.com

# 小汽配1.0报价单迁移至2.0 - 单条写入

"""
# code is far away from bugs with the god animal protecting
    I love animals. They taste delicious.
              ┏┓      ┏┓
            ┏┛┻━━━┛┻┓
            ┃      ☃      ┃
            ┃  ┳┛  ┗┳  ┃
            ┃      ┻      ┃
            ┗━┓      ┏━┛
                ┃      ┗━━━┓
                ┃  神兽保佑    ┣┓
                ┃ 永无BUG！   ┏┛
                ┗┓┓┏━┳┓┏┛
                  ┃┫┫  ┃┫┫
                  ┗┻┛  ┗┻┛ 
"""

import typing as t
import uuid

import pymysql
import threading
import time
from queue import Queue

from pydantic import BaseModel
from pymongo import MongoClient
from pymongo.errors import ConnectionFailure

from dbutils.pooled_db import PooledDB


class CarInfo(BaseModel):
    """车架号信息"""
    vin: t.Union[str, None] = ''
    mcid: t.Union[str, None] = ''
    brandCode: t.Union[str, None] = ''
    brand_logo: t.Union[str, None] = ''
    market_name: t.Union[str, None] = ''


class DTQualityPrice(BaseModel):
    """鼎腾零件品质"""
    quality_name: t.Union[str, None] = ''  # 品质名称
    quality_oe: t.Union[str, None] = ''  # 品质oe
    warranty: t.Union[str, None] = ''  # 质保
    purchase_day: t.Union[str, None] = ''  # 订货
    quality_price: t.Union[float, int, None] = 0  # 价格
    selected: t.Union[bool, None] = False  # 是否选中
    warehouse: t.Union[int, None] = 1  # 0上询 1仓发
    # 协议字段
    agreement_price: t.Union[int, None] = 0  # 协议价
    agreement_type: t.Union[str, None] = ''  # 协议类型
    # 鼎腾下单
    managerDiscount: t.Union[int, None] = 0  # 管家优惠金额，无优惠就传0
    deliverType: t.Union[int, None] = 0  # 1现货2调货3订货
    skuName: t.Union[str, None] = ''  # suk名称
    # 鼎腾上询字段
    supplierId: t.Union[int, str, None] = 0  # 调货/订货供应商id必传
    supplierName: t.Union[str, None] = ''  # 调货/订货供应商id必传
    supplierPrice: t.Union[int, None] = 0  # 调货/订货供应商供报价必传
    supplierOeCode: t.Union[str, None] = ''  # 供应商oe码--上询时供应商提供
    supplierSkuCode: t.Union[str, None] = ''  # 供应商商品编码--上询时供应商提供
    brandId: t.Union[int, None] = 0  # 品牌id
    brandName: t.Union[str, None] = ''  # 品牌id
    qualityId: t.Union[int, None] = 0  # 鼎腾品质ID
    qualityName: t.Union[str, None] = ''  # 鼎腾品质ID
    brandAndQualityDesc: t.Union[str, None] = ''  # 宝马/原厂原包
    taxQuotePrice: t.Union[float, None] = 0  # 含税报价
    supplyType: t.Union[int, None] = 0  # 供货方式 1备货/现货 2调货 3订货
    supplyTypeDesc: t.Union[str, None] = ''  # 供货方式描述
    stockOut: t.Union[int, None] = 0  # 1标记缺货0无标记
    stockOutDesc: t.Union[str, None] = ''  # 标记描述
    orderDays: t.Union[int, None] = 0  # 订货天数
    # 鼎腾库存
    skuId: t.Union[int, None] = 0  # 商品id,匹配到就得回传
    inventoryNum: t.Union[int, None] = 0  # 库存数
    guidePrice: t.Union[int, None] = 0  # 销售指导价
    guidePriceDesc: t.Union[str, None] = ''  # 销售指导价 描述
    guidePriceSource: t.Union[str, None] = ''  # 销售指导价计算描述
    guidePurchasePrice: t.Union[int, None] = 0  # 推荐采购价即内部结算价
    guidePurchasePriceDesc: t.Union[str, None] = ''  # 推荐采购价即内部结算价,金额描述
    guidePurchasePriceSource: t.Union[str, None] = ''  # 推荐采购价的计算方式
    part_name: t.Union[str, None] = ''  # 零件名称


class PartItem(BaseModel):
    """零件"""
    part_id: str
    part_name: str  # 询价名称
    oe: t.Union[str, None] = ''  # 零件号
    amount: t.Union[int, None] = 1  # 订货数
    price_4s: t.Union[float, None] = 0  # 4s价格
    unit: t.Union[str, None] = '个'  # 单位
    remark: t.Union[str, None] = ''
    # quality_price: t.List[QualityPrice] = []
    quality_price: t.Union[t.List[DTQualityPrice], None] = []
    # EPC 数据
    decode_oe: t.Union[str, None] = ''  # 译码oe
    mid: t.Union[str, None] = ''
    subgroup: t.Union[str, None] = ''
    pnum: t.Union[str, None] = ''
    break_img: t.Union[str, None] = ''
    qr_img: t.Union[str, None] = ''
    standard_label: t.Union[str, None] = ''
    pid_replacement: t.Union[t.List, None] = []
    img_point: t.Union[t.Dict, None] = {}
    img: t.Union[str, None] = ''


class InquiryUser(BaseModel):
    """询价员信息"""
    name: t.Union[str, None] = None
    user_name: t.Union[str, None] = None
    repair_name: t.Union[str, None] = None
    repair_shop_id: t.Union[str, None]
    user_level: t.Union[str, None] = None
    province_city: t.Union[str, None] = None
    address: t.Union[str, None] = None
    contact_user: t.Union[str, None] = None
    phone: t.Union[str, None] = None
    remark: t.Union[str, None] = None
    tax_number: t.Union[str, None] = None
    agreement_info: t.Union[t.Dict, None] = None


class ThreadInsert(object):
    """多线程并发Mongo插入数据"""

    def __init__(self):
        start_time = time.time()

        # 初始化数据库连接
        # 1.0生产库
        self.diablo_conn_1_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")
        # 2.0开发库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")
        # 测试mysql
        self.mysql_conn = self.get_mysql_connection()

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=50,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 50  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.47',
            user='users',
            port=3306,
            passwd='uSR@#H^&JD',
            db='users',
            use_unicode=True)
        return pool

    # ------------------ 格式化类 --------------------

    # 获取零件信息
    def get_format_part_list(self, part_list):

        format_part_list = []

        for part in part_list:

            quality_price = []

            # 库存数据清洗
            for part_price in part.get("quality_price", ""):

                quality_price_sub = part_price.get("quality_price", 0)
                if quality_price_sub == "" or quality_price_sub is None:
                    quality_price_sub = 0

                supplier_price = part_price.get("inventory_price", 0)
                if supplier_price == "" or supplier_price is None:
                    supplier_price = 0

                quality_price.append({
                    "part_name": part_price.get("part_name", ""),
                    "quality_name": part_price.get("quality_name", ""),
                    "quality_oe": part.get("oe", ""),
                    "warranty": part_price.get("warranty", ""),
                    "purchase_day": part_price.get("purchase_day", ""),
                    "quality_price": quality_price_sub,
                    "selected": True,
                    "warehouse": 1,
                    "supplierPrice":  supplier_price,
                    "inventoryNum": part_price.get("stocks_amount", 0),
                    "brandName": part_price.get("brand", ""),
                    "qualityName": part_price.get("ori_quality", ""),
                })

            # 库存数量特殊处理
            amount = part.get("amount", 0)
            if amount == "" or amount is None:
                amount = 0
            elif isinstance(amount, str):
                if amount.isdigit():
                    amount = int(amount)
                else:
                    amount = 0
            elif not isinstance(amount, int):
                amount = 0

            # 4s特殊价格处理
            price_4s = part.get("price", 0)
            if price_4s == "" or price_4s is None or price_4s == "暂无价格":
                price_4s = 0
            else:
                price_4s = float(price_4s)

            # part_id特殊处理
            part_id = part.get("decode_part_id", "")
            if part_id is None:
                part_id = str(uuid.uuid4())

            part_item = {
                "part_id": part_id,
                "part_name": part.get("origin_name", ""),
                "oe": part.get("oe", ""),
                "amount": amount,
                "price_4s": price_4s,
                "unit": part.get("unit", ""),
                "remark": part.get("remark", ""),
                "quality_price": quality_price,
                "mid": part.get("mid", ""),
                "subgroup": part.get("subgroup", ""),
                "pnum": part.get("pnum", ""),
                "break_img": part.get("break_img", ""),
                "qr_img": part.get("qr_img", ""),
                "standard_label": part.get("standard_label", ""),
                "pid_replacement": [],
                "img_point": part.get("img_point", {}),
                "img": "",
            }
            format_part_list.append(PartItem(**part_item).dict())

        return format_part_list

    # 获取user_id，因为迁移后的用户信息，user_id都被重新生成了，需要用手机号重新匹配
    def get_user_id(self, user_id=None):

        format_user_id = ''

        if user_id:

            mysql_conn = self.mysql_conn.connection()

            try:
                cursor = mysql_conn.cursor()

                sql = f"""select phone from diablo_member where id='{user_id}' limit 1"""

                cursor.execute(sql)

                result = cursor.fetchall()

                # print("phone:" + result)

                if len(result) > 0:
                    phone = result[0][0]

                    if phone:
                        # 查询2.0数据库中人员表operation_staff，匹配手机号对应的用户，返回user_id
                        connection = self.diablo_conn_2_0

                        try:
                            if connection is not None:
                                # 执行数据库操作
                                db = connection["ddll"]
                                conn = db["operation_staff"]

                                filter_dict = {
                                    'phone': phone
                                }
                                docs = conn.find(filter_dict, {'user_id': 1})

                                for doc in docs:
                                    format_user_id = doc.get("user_id", "")
                                    break

                        except Exception as e:
                            print("获取用户user_id信息失败 - mongo：", e)
                        finally:
                            # 将连接放回连接池
                            connection.close()

            except Exception as e:
                print("获取用户user_id信息失败 - mysql：", e)
            finally:
                mysql_conn.close()

        return format_user_id

    def get_repair_shop_info(self, company_name):
        # 查询2.0数据库中人员表wechat_repair_shop，匹配商家信息，并返回

        user_name = ""
        repair_shop_id = ""

        if company_name:
            connection = self.diablo_conn_2_0

            try:
                if connection is not None:
                    # 执行数据库操作
                    db = connection["ddll"]
                    conn = db["wechat_repair_shop"]

                    filter_dict = {
                        'name': company_name
                    }
                    docs = conn.find(filter_dict, {'user_name': 1, 'repair_shop_id': 1})

                    for doc in docs:
                        user_name = doc.get("user_name", "")
                        repair_shop_id = doc.get("repair_shop_id", "")
                        break

            except Exception as e:
                print("获取用户user_id信息失败 - mongo：", e)
            finally:
                # 将连接放回连接池
                connection.close()

        return user_name, repair_shop_id

    # 询价员信息格式化
    def format_inquiry_user(self, inquiry_user_data):

        format_inquiry_user = {}

        if inquiry_user_data:

            user_name, repair_shop_id = self.get_repair_shop_info(inquiry_user_data.get("company", ""))

            format_inquiry_user = {
                "name": inquiry_user_data.get("company", ""),
                "user_name": user_name,
                "repair_name": "",
                "repair_shop_id": repair_shop_id,
                "user_level": "普通客户",
                "province_city": inquiry_user_data.get("province", "") + "/" + inquiry_user_data.get("city", ""),
                "address": inquiry_user_data.get("address", ""),
                "contact_user": "",
                "phone": inquiry_user_data.get("phone", ""),
                "remark": "",
                "tax_number": "",
                "agreement_info": None
            }

        return format_inquiry_user

    # 直接从mongo获取迁移后的用户信息
    def get_operate_user_info_by_name(self, user_name=None):

        user_info = {}

        if user_name:

            # 查询2.0数据库中人员表operation_staff，返回用户信息
            connection = self.diablo_conn_2_0

            try:
                if connection is not None:
                    # 执行数据库操作
                    db = connection["ddll"]
                    conn = db["operation_staff"]

                    filter_dict = {
                        'real_name': user_name
                    }
                    docs = conn.find(filter_dict, {'user_id': 1, 'phone': 1, 'yc_id': 1, 'company_name': 1})

                    for doc in docs:
                        user_info = doc
                        break

            except Exception as e:
                print("获取用户user_id信息失败 - mongo：", e)
            finally:
                # 将连接放回连接池
                connection.close()

        return user_info

    # 写入新订单
    def ddl_inquiry_doc_format(self, doc):

        # 获取用户信息
        platform_user_info = self.get_operate_user_info_by_name(doc.get('platform_user', '').get("username", ''))
        transfer_user_info = self.get_operate_user_info_by_name(doc.get('transfer_user', '').get("username", ''))
        quote_user_info = self.get_operate_user_info_by_name(doc.get('quote_user', '').get("username", ''))

        inquiry_doc = {
            'inquiry_id': doc.get('inquiry_id'),
            'create_time': doc.get('create_time'),
            'inquiry_status': 6,
            'inquiry_status_desc': '待发货',

            'merchant_id': '3',
            'user_id': self.get_user_id(doc.get('uid', None)),

            'car_info': CarInfo(**doc['car_info']).dict(),  # 车架号信息
            'part_list': self.get_format_part_list(doc.get('part_list', None)),  # 零件列表
            'part_sku': len(doc.get('part_list')) if doc.get('part_list', None) else 0,  # 零件数量
            'inquiry_user': self.format_inquiry_user(doc.get('inquiry_user', None)),  # 询价用户
            'platform_user': {
                "uid": platform_user_info.get("user_id", ""),
                "merchant_id": doc.get('platform_user', '').get("merchant_id", ''),
                "username": doc.get('platform_user', '').get("username", ''),
                "phone": platform_user_info.get("phone", ""),
                "company": "宏德",
                "company_name": platform_user_info.get("company_name", "")
            },
            'create_source': "platform_inquiry",
            'remark_text': "",  # 备注文本
            'remark_tags': [{"tag_name": "加急", "tag_type": "rush", "tag_selected": False},
                            {"tag_name": "事故", "tag_type": "accident", "tag_selected": False},
                            {"tag_name": "保险", "tag_type": "insurance", "tag_selected": False},
                            {"tag_name": "发票", "tag_type": "invoice", "tag_selected": False},
                            {"tag_name": "大单", "tag_type": "considerable", "tag_selected": False}],  # 备注标签
            'insurance_info': {},  # 保险公司
            'reception_info': {},
            'pay_info': {},  # 订单金额 支付方式
            'buyer_info': {},
            'seller_info': {},
            'inquiry_info': {
                "license_plate": doc.get('inquiry_info', '').get("license_plate", ''),
                "part_img": doc.get('inquiry_info', '').get("part_img", ''),
                "part_media": doc.get('inquiry_info', '').get("part_media", ''),
                "part_text": doc.get('inquiry_info', '').get("part_text", ''),
                "phone": "",
                "real_name": ""
            },  # 询价信息
            'transfer_info': {"transfer_time": doc.get('transfer_info', '').get("transfer_time", '')},  # 译码信息
            'quote_info': {"quote_time": doc.get('quote_info', '').get("quote_time", '')},  # 报价信息
            'order_info': {},  # 订单信息
            'if_top': False,
            'transfer_user': {
                "user_id": transfer_user_info.get("user_id", ""),
                "phone": transfer_user_info.get("phone", ""),
                "real_name": doc.get('transfer_user', '').get("username", ''),
                "current_merchant_name": "宏德",
                "yc_id": transfer_user_info.get("yc_id", ""),
                "address": ""
            },
            'quote_user': {
                "user_id": quote_user_info.get("user_id", ""),
                "phone": quote_user_info.get("phone", ""),
                "real_name": doc.get('quote_user', '').get("username", ''),
                "current_merchant_name": "宏德",
                "yc_id": quote_user_info.get("yc_id", ""),
                "address": "",
            },
            'order_user': {},
        }

        # print(inquiry_doc)

        return inquiry_doc

    # 插入2.0MongoDB数据库
    def mongo_insert(self, *args):

        doc = args[0]

        # 清洗数据并拼装返回
        format_coc = self.ddl_inquiry_doc_format(doc)

        # 获取2.0MongoDB连接
        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["ddl_inquiry"]
                conn.insert_one(dict(format_coc))

        except Exception as e:
            print("写入2.0MongoDB数据库单据失败：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 获取1.0已报价报价单，生成器迭代返回数据
    def return_diablo_1_0_data(self):

        connection = self.diablo_conn_1_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["diablo"]
                conn = db["quote"]

                filter_dict = {
                    'inquiry_user.merchant_id': '3',
                    'quote_info.status': 3
                }
                docs = conn.find(filter_dict)

                previous = next(docs, None)
                for doc in docs:
                    yield previous, False
                    previous = doc
                yield previous, True

                # for doc in docs:
                #     yield dict(doc)

        except Exception as e:
            print("获取1.0询报价单据失败：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 任务执行
    def task(self):
        q = Queue(maxsize=200)  # 设定最大队列数和线程数

        count = 1

        for doc, last in self.return_diablo_1_0_data():
            t = threading.Thread(target=self.mongo_insert, args=(doc,))
            q.put(t)
            if (q.full() == True) or last:
                thread_list = []
                while q.empty() == False:
                    t = q.get()
                    thread_list.append(t)
                    t.start()
                for t in thread_list:
                    t.join()

            print('写入数据量:', count, '当前写入询价单:' + doc.get("inquiry_id"))

            if last:
                break

            count += 1


if __name__ == '__main__':
    ThreadInsert()
